import time
import os
import pymysql
from pymysql.err import OperationalError, ProgrammingError
from tool.get_log import GetLogging
from dbutils.pooled_db import PooledDB

logger = GetLogging().get_logging()


class DatabaseProcess:
    DB_CONFIG = {
        "host": "192.168.153.131",
        "port": 3306,
        "user": "tastshop",
        "password": "123456",
        "database": "tastshop",
        "charset": "utf8mb4",
        "cursorclass": pymysql.cursors.DictCursor,
        "autocommit": False
    }

    @classmethod
    def init_pool(cls):
        try:
            cls.pool = PooledDB(
                creator=pymysql,
                maxconnections=20,
                mincached=5,
                maxcached=10,
                blocking=True,
                maxusage=None,
                setsession=["SET NAMES utf8mb4"],
                **cls.DB_CONFIG
            )
            logger.info("数据库连接池初始化成功")
        except Exception as e:
            logger.error(f"数据库连接池初始化失败：{str(e)}", exc_info=True)
            raise

    def __enter__(self):
        self.conn = self.pool.connection()
        self.cursor = self.conn.cursor()
        logger.info("从连接池获取连接成功")
        return self

    def __exit__(self, exc_type, exc_val, exc_tb):
        try:
            if exc_type:
                if isinstance(exc_val, (OperationalError, ProgrammingError)):
                    self.conn.rollback()
                    logger.warning(f"数据库操作异常，已回滚事务：{str(exc_val)}")
                else:
                    logger.warning(f"业务逻辑异常，事务未回滚：{str(exc_val)}")
            else:
                self.conn.commit()
                logger.info("事务已提交")
        finally:
            if self.cursor:
                self.cursor.close()
                logger.info("游标已关闭")
            if self.conn:
                self.conn.close()
                logger.info("连接已归还到连接池")
        return False

    def get_max_id(self, table, condition=None):
        try:
            sql = f"SELECT MAX(id) AS max_id FROM `{table}`"
            params = []
            if condition:
                sql += f" WHERE {condition}"
            self.cursor.execute(sql, params)
            result = self.cursor.fetchone()
            max_id = result["max_id"] if result["max_id"] is not None else 0
            logger.info(f"获取最大ID成功：表={table}，条件={condition}，ID={max_id}")
            return max_id
        except Exception as e:
            logger.error(f"获取最大ID失败：表={table}，条件={condition}", exc_info=True)
            raise

    def delete_register_user(self, min_user_id=48):
        try:
            delete_sql = "DELETE FROM sxo_user WHERE `id` >= %s"
            self.cursor.execute(delete_sql, (min_user_id,))
            logger.info(f"成功删除用户，删除条件：ID >= {min_user_id}，影响行数：{self.cursor.rowcount}")
        except Exception as e:
            logger.error(f"删除用户失败，条件：ID >= {min_user_id}", exc_info=True)
            raise

    def update_order_express(self, status=3, pay_status=1, pay_price=11000, order_id=0, user_id=47, express_id=1,
                             express_number=498919):
        try:
            current_time = int(time.time())
            update_sql = "UPDATE `sxo_order` SET `status` = %s, `pay_status` = %s, `pay_price` = %s WHERE `id` = %s"
            self.cursor.execute(update_sql, (status, pay_status, pay_price, order_id))
            insert_sql = """
                INSERT INTO sxo_order_express 
                (order_id, user_id, express_id, express_number, add_time) 
                VALUES (%s, %s, %s, %s, %s)
            """
            self.cursor.execute(insert_sql, (order_id, user_id, express_id, express_number, current_time))
            logger.info(
                f"发货成功：订单ID={order_id}，状态={status}，快递单号={express_number}，"
                f"更新订单行数：{self.cursor.rowcount}"
            )
        except Exception as e:
            logger.error(f"发货失败：订单ID={order_id}", exc_info=True)
            raise

    def update_order_aftersale(self, status=1, order_after_id=0, refundment=0):
        try:
            update_sql = "UPDATE `sxo_order_aftersale` SET `status` = %s, `refundment` = %s WHERE `id` = %s"
            self.cursor.execute(update_sql, (status, refundment, order_after_id))
            if self.cursor.rowcount == 0:
                logger.warning(f"未找到需更新的售后订单：ID={order_after_id}")
            else:
                logger.info(f"确认退货成功：售后ID={order_after_id}，状态={status}")
        except Exception as e:
            logger.error(f"确认退货失败：售后ID={order_after_id}", exc_info=True)
            raise

    def get_database_order(self):
        return self.get_max_id("sxo_order")

    def get_database_user(self):
        return self.get_max_id("sxo_user")

    def get_database_user_address(self):
        return self.get_max_id("sxo_user_address")

    def get_database_order_aftersale(self):
        return self.get_max_id("sxo_order_aftersale")

    def get_database_product_warehouse(self):
        return self.get_max_id("sxo_warehouse_goods")

    def get_database_product(self):
        return self.get_max_id("sxo_goods")

    def get_cart_max_id(self, user_id=1):
        return self.get_max_id("sxo_cart", condition=f"user_id={user_id}")

    def format_xpath_with_order_id(self, xpath_template):
        max_order_id = self.get_database_order()
        return xpath_template.format(order_id=max_order_id)

    def format_xpath_with_cart_id(self, xpath_template, user_id=1):
        max_cart_id = self.get_cart_max_id(user_id=user_id)
        return xpath_template.format(user_cart_id=max_cart_id)

    def format_xpath_with_user_id(self, xpath_template):
        user_id = self.get_database_user()
        return xpath_template.format(user_id=user_id)

    def format_xpath_with_address_id(self, xpath_template):
        address_id = self.get_database_user_address()
        return xpath_template.format(address_id=address_id)

    def format_xpath_with_product_warehouse_id(self, xpath_template):
        warehouse_id = self.get_database_product_warehouse()
        return xpath_template.format(warehouse_id=warehouse_id)

    def format_xpath_with_product_id(self, xpath_template):
        product_id = self.get_database_product()
        return xpath_template.format(product_id=product_id)

    def check_user_exist(self, username):
        if not username:
            raise ValueError("必须提供用户名")
        try:
            sql = "SELECT id FROM sxo_user WHERE username = %s"
            params = (username,)
            query_by = f"用户名={username}"
            self.cursor.execute(sql, params)
            result = self.cursor.fetchone()
            logger.info(result)
            exists = bool(result)
            if exists:
                logger.info(f"用户存在：{query_by}")
            else:
                logger.info(f"用户不存在：{query_by}")

            return exists
        except Exception as e:
            logger.error(f"检查用户{username}存在失败", exc_info=True)
            raise

    def get_user_info(self, username):
        if not username:
            raise ValueError("必须提供用户名")
        try:
            sql = "SELECT * FROM sxo_user WHERE username = %s"
            params = (username,)
            query_by = f"用户名={username}"
            self.cursor.execute(sql, params)
            user_info = self.cursor.fetchone()
            if user_info:
                logger.info(f"获取用户信息成功：{query_by}")
            else:
                logger.info(f"未找到用户信息：{query_by}")
            return user_info
        except Exception as e:
            logger.error(f"获取用户{username}信息失败", exc_info=True)
            raise

    def check_user_data_changed(self, username, expected_changes):
        if not username:
            raise ValueError("必须提供用户名")
        if not expected_changes or not isinstance(expected_changes, dict):
            raise ValueError("预期变化必须是非空字典")
        try:
            current_user = self.get_user_info(username)
            if not current_user:
                return False, []
            change_results = []
            for field, expected_value in expected_changes.items():
                if field not in current_user:
                    change_results.append((field, False, "字段不存在", None, expected_value))
                    continue
                current_value = current_user[field]
                is_match = (current_value == expected_value)
                change_results.append((field, is_match, "", current_value, expected_value))
                if not is_match:
                    logger.warning(f"用户字段不匹配：用户名={username}, 字段={field}, "
                                   f"实际值={current_value}, 预期值={expected_value}")
            all_matched = all(result[1] for result in change_results)
            if all_matched:
                logger.info(f"用户字段变化符合预期：用户名={username}")
            else:
                logger.info(f"用户字段变化不符合预期：用户名={username}")
            return all_matched, change_results
        except Exception as e:
            logger.error(f"检查用户数据变化失败：用户名={username}", exc_info=True)
            raise

    def check_address_added(self, user_id, expected_address_info):
        if not user_id:
            raise ValueError("必须提供用户ID")
        if not expected_address_info or not isinstance(expected_address_info, dict):
            raise ValueError("预期地址信息必须是非空字典")
        try:
            all_addresses = self.get_user_address(user_id)
            if not all_addresses:
                logger.warning(f"用户无任何地址，添加失败：用户ID={user_id}")
                return False
            print(all_addresses)
            for address in all_addresses:
                all_fields_match = True
                for field, expected_value in expected_address_info.items():
                    if field not in address:
                        all_fields_match = False
                        break
                    if address[field] != expected_value:
                        all_fields_match = False
                        break
                if all_fields_match:
                    logger.info(f"地址添加成功且信息匹配：用户ID={user_id}, 地址ID={address.get('id')}")
                    return True
            logger.warning(f"未找到匹配的地址信息：用户ID={user_id}, 预期信息={expected_address_info}")
            return False

        except Exception as e:
            logger.error(f"检查地址添加失败：用户ID={user_id}", exc_info=True)
            raise

    def get_user_address(self, user_id, address_id=None):
        if not user_id:
            raise ValueError("必须提供用户ID")
        try:
            if address_id:
                sql = "SELECT * FROM sxo_user_address WHERE user_id = %s AND id = %s"
                params = (user_id, address_id)
                query_by = f"用户ID={user_id}, 地址ID={address_id}"
                self.cursor.execute(sql, params)
                address_info = self.cursor.fetchone()
            else:
                sql = "SELECT * FROM sxo_user_address WHERE user_id = %s"
                params = (user_id,)
                query_by = f"用户ID={user_id}"
                self.cursor.execute(sql, params)
                address_info = self.cursor.fetchall()

            if address_info:
                logger.info(f"获取用户地址成功：{query_by}")
            else:
                logger.info(f"未找到用户地址：{query_by}")

            return address_info
        except Exception as e:
            logger.error(f"获取用户地址失败：用户ID={user_id}", exc_info=True)
            raise

    def check_address_data_changed(self, user_id, address_id, expected_changes):
        if not user_id:
            raise ValueError("必须提供用户ID")
        if not address_id:
            raise ValueError("必须提供地址ID")
        if not expected_changes or not isinstance(expected_changes, dict):
            raise ValueError("预期变化必须是非空字典")
        try:
            current_address = self.get_user_address(user_id, address_id)
            if not current_address:
                logger.warning(f"地址不存在，无法检查变化：用户ID={user_id}, 地址ID={address_id}")
                return False

            all_matched = True
            for field, expected_value in expected_changes.items():
                if field not in current_address:
                    logger.warning(f"地址字段不存在：用户ID={user_id}, 地址ID={address_id}, 字段={field}")
                    all_matched = False
                    continue

                current_value = current_address[field]
                if current_value != expected_value:
                    logger.warning(f"地址字段不匹配：用户ID={user_id}, 地址ID={address_id}, 字段={field}, "
                                   f"实际值={current_value}, 预期值={expected_value}")
                    all_matched = False

            if all_matched:
                logger.info(f"地址字段变化符合预期：用户ID={user_id}, 地址ID={address_id}")
            else:
                logger.info(f"地址字段变化不符合预期：用户ID={user_id}, 地址ID={address_id}")

            return all_matched
        except Exception as e:
            logger.error(f"检查地址数据变化失败：用户ID={user_id}, 地址ID={address_id}", exc_info=True)
            raise

    def check_product_exist(self, product_name):
        if not product_name:
            raise ValueError("必须提供商品名称")
        try:
            sql = "SELECT id FROM sxo_goods WHERE title = %s"
            params = (product_name,)
            query_by = f"商品名={product_name}"
            self.cursor.execute(sql, params)
            result = self.cursor.fetchone()
            logger.info(result)
            exists = bool(result)
            if exists:
                logger.info(f"商品存在：{query_by}")
            else:
                logger.info(f"商品不存在：{query_by}")

            return exists
        except Exception as e:
            logger.error(f"检查商品：{product_name}存在失败", exc_info=True)
            raise

    def get_product_info(self, product_name):
        if not product_name:
            raise ValueError("必须提供商品名")
        try:
            sql = "SELECT * FROM sxo_goods WHERE title = %s"
            params = (product_name,)
            query_by = f"商品名={product_name}"
            self.cursor.execute(sql, params)
            product_info = self.cursor.fetchone()
            if product_info:
                logger.info(f"获取商品信息成功：{query_by}")
            else:
                logger.info(f"获取商品信息失败：{query_by}")

            return product_info
        except Exception as e:
            logger.error(f"获取商品{product_name}信息失败", exc_info=True)
            raise

    def check_product_data_changed(self, product_name, expected_changes):
        if not product_name:
            raise ValueError("必须提供商品名称")
        if not expected_changes or not isinstance(expected_changes, dict):
            raise ValueError("预期变化必须是非空字典")
        try:
            current_product = self.get_product_info(product_name)
            if not current_product:
                logger.warning(f"商品不存在，无法检查变化：商品名={product_name}")
                return False

            all_matched = True
            for field, expected_value in expected_changes.items():
                if field not in current_product:
                    logger.warning(f"商品字段不存在：商品名={product_name}, 字段={field}")
                    all_matched = False
                    continue

                current_value = current_product[field]
                if current_value != expected_value:
                    logger.warning(f"商品字段不匹配：商品名={product_name}, 字段={field}, "
                                   f"实际值={current_value}, 预期值={expected_value}")
                    all_matched = False

            if all_matched:
                logger.info(f"商品字段变化符合预期：商品名={product_name}")
            else:
                logger.info(f"商品字段变化不符合预期：商品名={product_name}")

            return all_matched
        except Exception as e:
            logger.error(f"检查商品数据变化失败：商品名={product_name}", exc_info=True)
            raise

    def check_warehouse_product(self, product_name, expected_stock=None):
        if not product_name:
            raise ValueError("必须提供商品名称")

        try:
            product_info = self.get_product_info(product_name)
            if not product_info:
                logger.warning(f"商品不存在，仓库无对应记录：商品名={product_name}")
                return False
            product_id = product_info.get("id")
            if not product_id:
                logger.warning(f"商品无ID，无法关联仓库：商品名={product_name}")
                return False
            sql = "SELECT * FROM sxo_warehouse_goods WHERE goods_id = %s"
            params = (product_id,)
            self.cursor.execute(sql, params)
            warehouse_product = self.cursor.fetchone()
            if not warehouse_product:
                logger.warning(f"仓库中不存在该商品：商品名={product_name}, 商品ID={product_id}")
                return False
            logger.info(f"仓库商品存在：商品名={product_name}, 商品ID={product_id}, 仓库记录ID={warehouse_product.get('id')}")
            if expected_stock is not None:
                actual_stock = warehouse_product.get("inventory", 0)
                if actual_stock != expected_stock:
                    logger.warning(f"仓库商品库存不匹配：商品名={product_name}, 实际库存={actual_stock}, 预期库存={expected_stock}")
                    return False
                logger.info(f"仓库商品库存符合预期：商品名={product_name}, 库存={actual_stock}")

            return True
        except Exception as e:
            logger.error(f"检查仓库商品失败：商品名={product_name}", exc_info=True)
            raise

    def get_aftersale_status(self, aftersale_id):
        if not aftersale_id:
            raise ValueError("必须提供订单售后id")
        try:
            sql = "SELECT status FROM sxo_order_aftersale WHERE id = %s"
            params = (aftersale_id,)
            self.cursor.execute(sql, params)
            result = self.cursor.fetchone()
            return result['status']
        except Exception as e:
            logger.error(f"查询售后单状态失败！售后ID: {aftersale_id}, 错误信息: {str(e)}", exc_info=True)
            raise

    def get_order_status(self, order_id):
        if not order_id:
            raise ValueError("必须提供订单id")
        try:
            sql = "SELECT status FROM sxo_order WHERE id = %s"
            params = (order_id,)
            self.cursor.execute(sql, params)
            result = self.cursor.fetchone()
            return result['status']
        except Exception as e:
            logger.error(f"查询订单状态失败！订单ID: {order_id}, 错误信息: {str(e)}", exc_info=True)
            raise

DatabaseProcess.init_pool()
